﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DB.DataHelper
{
    /// <summary>
    /// sql连接基础连接辅助类
    /// </summary>
    public class SqlDataHelper : ISqlDataHelperService
    {
        private SqlConnection cn;

        private static string _SqlServiceConnectionString;

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void Opensql()
        {
            if (cn == null)
            {
                cn = new SqlConnection(_SqlServiceConnectionString);
            }
            if (cn.State == ConnectionState.Closed)
            {
                try
                {
                    cn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception("数据库连接失败，请检查连接字符串！" + ex.Message);
                }
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void Closesql()
        {
            if (cn != null)
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }


        /// <summary>
        /// 执行select语句，没有数组参数
        /// </summary>
        /// <param name="strSql">select语句</param>
        /// <returns>返回数组集</returns>
        private DataSet ExcuteDataSetSql(string strSql)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand(strSql, cn);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "e");
                return dataSet;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 执行select语句的查询方法，有参数
        /// </summary>
        /// <param name="strsql">select语句</param>
        /// <param name="sqlParameter">数组参数</param>
        /// <returns>返回数据集</returns>
        private DataSet ExcuteDataSetSql(string strsql, SqlParameter[] sqlParameter)
        {
            try
            {
                SqlCommand sqlCommend = new SqlCommand(strsql, cn);
                sqlCommend.Parameters.AddRange(sqlParameter);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommend;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "e");
                return dataSet;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 操作数据库不返回记录集（insert，update，delete等）
        /// </summary>
        /// <param name="str">执行的SQL语句</param>
        /// <param name="sql">数组参数</param>
        /// <returns></returns>
        private int ExecuteNonQuery(string str, SqlParameter[] sql)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand(str, cn);
                sqlCommand.Parameters.AddRange(sql);
                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 执行SQL，返回影响的行数
        /// </summary>
        public int ExecuteNonQuery(string assql)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(assql, cn);
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        string ISqlDataHelperService.SqlServiceConnectionString
        {
            set { _SqlServiceConnectionString = value; }
        }
        /// <summary>
        /// 设置 SQL数据库 连接字符串
        /// </summary>
        /// <param name="dataSource">服务器名称</param>
        /// <param name="initialCatalog">数据库名称</param>
        /// <param name="isVerification"> true windows验证 不需要用户名和密码 false sql service 验证 需要用户名密码</param>
        /// <param name="user">用户名</param>
        /// <param name="password">登录密码</param>
        /// <returns></returns>
        public void SetSqlServiceConnectionString(string dataSource, string initialCatalog, bool isVerification, string user, string password)
        {
            string sqlConString = @"Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Integrated Security=" + isVerification + ";User Id=" + user + ";Password=" + password + ";";
            _SqlServiceConnectionString = sqlConString;
        }
        public DataTable GetDataTable(string strsql)
        {
            Opensql();
            DataTable dt = new DataTable();
            DataSet ds = ExcuteDataSetSql(strsql);
            dt = ds.Tables[0];
            Closesql();
            return dt;
        }
        public DataTable GetDataTable(string strsql, SqlParameter[] sqlParameter)
        {
            Opensql();
            DataTable dt = new DataTable();
            DataSet ds = ExcuteDataSetSql(strsql, sqlParameter);
            dt = ds.Tables[0];
            Closesql();
            return dt;

        }

        public void ExecuteSql(string assql)
        {
            Opensql();
            try
            {
                int row = ExecuteNonQuery(assql);
                if (row <= 0)
                {
                    throw new Exception("执行sql 失败");
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            Closesql();
        }
        public void ExecuteSql(string assql, SqlParameter[] sqlParameter)
        {
            Opensql();
            try
            {
                int row = ExecuteNonQuery(assql, sqlParameter);
                if (row <= 0)
                {
                    throw new Exception("执行sql 失败");
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            Closesql();
        }
    }
}
